import com.fasterxml.jackson.annotation.JsonIgnoreProperties
import com.fasterxml.jackson.databind.ObjectMapper
import com.fasterxml.jackson.module.kotlin.readValue
import com.fasterxml.jackson.module.kotlin.registerKotlinModule
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.File
import java.math.BigDecimal
import java.math.RoundingMode

@JsonIgnoreProperties(ignoreUnknown = true)
data class PlayerData(
    val openId: String,
    var areaId: Int = 1,
    val castleLevel: Int,
    val online_time: Double,
    val roleId: Int,
    val roleName: String,
    val create_ts: String,
    val country: String?,
    val logintime_str: String?,
    val offlineTime_str: String?,
    val dayAllOnlineTime: Double?,
)

// 添加一个包装类来匹配JSON结构
@JsonIgnoreProperties(ignoreUnknown = true)
data class JsonData<T>(
    val rt: Int,
    val errorMsg: String,
    val data: Data<T>
)
@JsonIgnoreProperties(ignoreUnknown = true)
data class Data<T>(
    val data: List<T>
)

@JsonIgnoreProperties(ignoreUnknown = true)
data class CreateRoleData(
    val openId: String,
    var areaId: Int = 1,
    val castleLevel: Int,
    val online_time: Double,
    val roleId: Int,
    val roleName: String,
    val create_ts: String,
    val country: String = "",
    val serverId: String,
)

lateinit var userIdCountryMap : Map<String, String>
fun main() {
    userIdCountryMap = readUserIdCountryMap("E:\\数据\\KOD_创建角色明细20250825至今.xlsx")
    println("读取到 ${userIdCountryMap.size} 条记录")

    val excelUtils = ExcelUtils()
//    excelUtils.convertJsonToExcel(
//        "E:\数据\\loginOfflineTime.json",
//        "E:\数据\\注册用户登录退出详情.xlsx")

    excelUtils.registerDataToExcel(
        "E:\\数据\\data.json",
        "E:\\数据\\1&2区8.28-9.2注册用户信息.xlsx")
}

class ExcelUtils {
    private val mapper = ObjectMapper().registerKotlinModule()

    fun convertJsonToExcel(jsonFilePath: String, excelFilePath: String) {
        // 读取JSON数据
        val jsonData = File(jsonFilePath).readText()
        // 修改为读取正确的数据结构
        val data: JsonData<PlayerData> = mapper.readValue<JsonData<PlayerData>>(jsonData)
        val playerList = data.data.data

        // 创建工作簿和工作表
        val workbook = XSSFWorkbook()
        val sheet = workbook.createSheet("Player Data")

        // 创建表头
        val headerRow = sheet.createRow(0)
        headerRow.createCell(0).setCellValue("openId")
        headerRow.createCell(1).setCellValue("castleLevel")
        headerRow.createCell(2).setCellValue("online_time（min）")
        headerRow.createCell(3).setCellValue("game_server_id")
        headerRow.createCell(4).setCellValue("country")
        headerRow.createCell(5).setCellValue("create_time")
        headerRow.createCell(6).setCellValue("roleId")
        headerRow.createCell(7).setCellValue("roleName")
        headerRow.createCell(8).setCellValue("logintime_str")
        headerRow.createCell(9).setCellValue("offlineTime_str")
        headerRow.createCell(10).setCellValue("当天在线总时长")

        // 设置列宽
        sheet.setColumnWidth(0, 10 * 256)
        sheet.setColumnWidth(1, 10 * 256)
        sheet.setColumnWidth(2, 20 * 256)
        sheet.setColumnWidth(3, 10 * 256)
        sheet.setColumnWidth(4, 10 * 256)
        sheet.setColumnWidth(5, 20 * 256)
        sheet.setColumnWidth(6, 10 * 256)
        sheet.setColumnWidth(7, 10 * 256)
        sheet.setColumnWidth(8, 20 * 256)
        sheet.setColumnWidth(9, 20 * 256)
        sheet.setColumnWidth(10, 20 * 256)

        // 填充数据
        playerList.forEachIndexed { index, player ->
            val row = sheet.createRow(index + 1)

            val country = userIdCountryMap[player.openId]

            row.createCell(0).setCellValue(player.openId)
            row.createCell(1).setCellValue(player.castleLevel.toString())
            
            // 保留两位小数
            val roundedOnlineTime = BigDecimal(player.online_time.toString())
                .setScale(2, RoundingMode.HALF_UP)
                .toDouble()
            row.createCell(2).setCellValue(roundedOnlineTime)

            row.createCell(3).setCellValue("1")
            row.createCell(4).setCellValue(country ?: player.country)

            row.createCell(5).setCellValue(player.create_ts)
            row.createCell(6).setCellValue(player.roleId.toString())
            row.createCell(7).setCellValue(player.roleName)
            row.createCell(8).setCellValue(player.logintime_str)
            row.createCell(9).setCellValue(player.offlineTime_str)

            row.createCell(10).setCellValue(player.dayAllOnlineTime.toString())
        }

        // 保存Excel文件
        File(excelFilePath).outputStream().use { outputStream ->
            workbook.write(outputStream)
        }
        workbook.close()
    }

    fun registerDataToExcel(jsonFilePath: String, excelFilePath: String) {
        // 读取JSON数据
        val jsonData = File(jsonFilePath).readText()
        // 修改为读取正确的数据结构
        val data: JsonData<CreateRoleData> = mapper.readValue<JsonData<CreateRoleData>>(jsonData)
        val playerList = data.data.data

        // 创建工作簿和工作表
        val workbook = XSSFWorkbook()
        val sheet = workbook.createSheet("Player Data")

        // 创建表头
        val headerRow = sheet.createRow(0)
        headerRow.createCell(0).setCellValue("openId")
        headerRow.createCell(1).setCellValue("castleLevel")
        headerRow.createCell(2).setCellValue("online_time（min）")
        headerRow.createCell(3).setCellValue("game_server_id")
        headerRow.createCell(4).setCellValue("country")
        headerRow.createCell(5).setCellValue("create_time")
        headerRow.createCell(6).setCellValue("roleId")
        headerRow.createCell(7).setCellValue("roleName")

        // 设置列宽
        sheet.setColumnWidth(0, 10 * 256)
        sheet.setColumnWidth(1, 10 * 256)
        sheet.setColumnWidth(2, 20 * 256)
        sheet.setColumnWidth(3, 10 * 256)
        sheet.setColumnWidth(4, 10 * 256)
        sheet.setColumnWidth(5, 20 * 256)
        sheet.setColumnWidth(6, 10 * 256)
        sheet.setColumnWidth(7, 10 * 256)

        // 填充数据
        playerList.forEachIndexed { index, player ->
            val row = sheet.createRow(index + 1)

            val country = userIdCountryMap[player.openId]

            row.createCell(0).setCellValue(player.openId)
            row.createCell(1).setCellValue(player.castleLevel.toString())

            // 保留两位小数
            val roundedOnlineTime = BigDecimal(player.online_time.toString())
                .setScale(2, RoundingMode.HALF_UP)
                .toDouble()
            row.createCell(2).setCellValue(roundedOnlineTime)

            row.createCell(3).setCellValue(player.serverId)
            row.createCell(4).setCellValue(country ?: player.country)

            row.createCell(5).setCellValue(player.create_ts)
            row.createCell(6).setCellValue(player.roleId.toString())
            row.createCell(7).setCellValue(player.roleName)
        }

        // 保存Excel文件
        File(excelFilePath).outputStream().use { outputStream ->
            workbook.write(outputStream)
        }
        workbook.close()
    }

}



/**
 * 读取Excel文件，建立userId和country的映射关系
 * @param filePath Excel文件路径
 * @return userId到country的映射Map
 */
fun readUserIdCountryMap(filePath: String): Map<String, String> {
    val userIdCountryMap = mutableMapOf<String, String>()

    File(filePath).inputStream().use { inputStream ->
        val workbook = XSSFWorkbook(inputStream)
        val sheet = workbook.getSheetAt(0) // 获取第一个工作表

        // 跳过第一行（表头），从第二行开始读取数据
        for (rowIndex in 1 until sheet.physicalNumberOfRows) {
            val row = sheet.getRow(rowIndex)
            if (row != null) {
                // 读取A列(userId)和B列(country)
                val userIdCell = row.getCell(0) // A列
                val countryCell = row.getCell(1) // B列

                val userId = userIdCell?.rawValue?.trim()
                val country = countryCell?.toString()?.trim()

                // 只有当userId和country都不为空时才添加到映射中
                if (!userId.isNullOrEmpty() && !country.isNullOrEmpty()) {
                    userIdCountryMap[userId] = country
                }
            }
        }

        workbook.close()
    }

    return userIdCountryMap
}

